CREATE TABLE jms_dwd.dwd_center_scan_lack_detail (
                                                        dt                        date COMMENT '日期',
                                                        billcode varchar(100) comment '运单号',
                                                        scan_code varchar(80) comment '操作中心编码',
                                                        order_source_name varchar(300) comment '订单来源名称',
                                                        agent_code                varchar(80) comment '操作中心代理区编码',
                                                        mage_region_code          varchar(80) comment '管理大区编码',
                                                        terminal_dispatch_code varchar(200) comment '三段码编码',
                                                        scan_name varchar(300) comment '操作中心名称',
                                                        agent_name                varchar(300) comment '操作中心代理区名称',
                                                        mage_region_name          varchar(300) comment '管理大区名称',
                                                        arrival_cnt int comment '到件次数次数为0为到件漏扫',
                                                        arrival_time datetime comment '到件时间',
                                                        arrival_packagecode varchar(300) comment '到件包号',
                                                        arrival_pack_create_time datetime comment '到件建包时间',
                                                        arrival_pack_start_code varchar(80) comment '到件建包网点编码',
                                                        arrival_pack_start_name varchar(300) comment '到件建包网点名称',
                                                        arrival_pack_end_code varchar(80) comment '到件拆包网点编码',
                                                        arrival_pack_end_name varchar(300) comment '到件拆包网点名称',
                                                        arrival_transfercode varchar(300) comment '到件任务号',
                                                        arrival_shipment_name varchar(300) comment '到件任务名',
                                                        arrival_scanusercode varchar(300) comment '到件操作员',
                                                        arrival_scanuser varchar(300) comment '到件操作员',
                                                        send_cnt int comment '发件次数次数为0为发件漏扫',
                                                        send_time datetime comment '发件时间',
                                                        send_packagecode varchar(300) comment '发件包号',
                                                        send_pack_start_code varchar(80) comment '发件建包网点编码',
                                                        send_pack_start_name varchar(300) comment '发件建包网点名称',
                                                        send_scanusercode varchar(300) comment '发件操作员',
                                                        send_scanuser varchar(300) comment '发件操作员',
                                                        lst_scan_code varchar(80) comment '上一站编码',
                                                        lst_scan_name varchar(300) comment '上一站名称',
                                                        nxt_scan_code varchar(80) comment '下一站编码',
                                                        nxt_scan_name varchar(300) comment '下一站名称',
                                                        send_lack_packagecode varchar(300) comment '发件漏扫包号',
                                                        send_lack_pack_start_code varchar(80) comment '发件漏扫建包网点编码',
                                                        send_lack_pack_start_name varchar(300) comment '发件漏扫建包网点名称',
                                                        order_source_code varchar(80) comment '订单来源编码'
) ENGINE=OLAP
    DUPLICATE KEY(`dt`, `billcode`,`scan_code`,`order_source_name`,`agent_code`,`mage_region_code`)
    COMMENT '转运中心到发漏扫明细'
    PARTITION BY RANGE(`dt`)
    (
    PARTITION  p20220212 VALUES [("2022-02-12"), ("2022-02-13")),
    PARTITION  p20220213 VALUES [("2022-02-13"), ("2022-02-14")),
    PARTITION  p20220214 VALUES [("2022-02-14"), ("2022-02-15")),
    PARTITION  p20220215 VALUES [("2022-02-15"), ("2022-02-16")),
    PARTITION  p20220216 VALUES [("2022-02-16"), ("2022-02-17")),
    PARTITION  p20220217 VALUES [("2022-02-17"), ("2022-02-18")),
    PARTITION  p20220218 VALUES [("2022-02-18"), ("2022-02-19")),
    PARTITION  p20220219 VALUES [("2022-02-19"), ("2022-02-20")),
    PARTITION  p20220220 VALUES [("2022-02-20"), ("2022-02-21")),
    PARTITION  p20220221 VALUES [("2022-02-21"), ("2022-02-22")),
    PARTITION  p20220222 VALUES [("2022-02-22"), ("2022-02-23")),
    PARTITION  p20220223 VALUES [("2022-02-23"), ("2022-02-24")),
    PARTITION  p20220224 VALUES [("2022-02-24"), ("2022-02-25")),
    PARTITION  p20220225 VALUES [("2022-02-25"), ("2022-02-26")),
    PARTITION  p20220226 VALUES [("2022-02-26"), ("2022-02-27")),
    PARTITION  p20220227 VALUES [("2022-02-27"), ("2022-02-28")),
    PARTITION  p20220228 VALUES [("2022-02-28"), ("2022-03-01")),
    PARTITION  p20220301 VALUES [("2022-03-01"), ("2022-03-02")),
    PARTITION  p20220302 VALUES [("2022-03-02"), ("2022-03-03")),
    PARTITION  p20220303 VALUES [("2022-03-03"), ("2022-03-04")),
    PARTITION  p20220304 VALUES [("2022-03-04"), ("2022-03-05")),
    PARTITION  p20220305 VALUES [("2022-03-05"), ("2022-03-06"))
    )
    DISTRIBUTED BY HASH(`billcode`) BUCKETS 10
    PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10",
    "in_memory" = "false",
    "storage_format" = "V2"
    );
